package ee.ut.battleship;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class SQL
 * 
 * Connection for getting highscores from DB
 */
@WebServlet("/SQL")
public class SQL extends HttpServlet {
	static String username = "root", password = "g8HwwDSFX2";
	static String url = "jdbc:mysql://mysql-gamebattleship.jelastic.dogado.eu/battleship";
	
//	static String username = "root", password = "0000";
//	static String url = "jdbc:mysql://localhost:3306";
	
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SQL() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		String awnser = "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url,username,password);
            Statement stmt = conn.createStatement();
            ResultSet rs;
            rs = stmt.executeQuery("SELECT name,wins,games FROM highscore order by (wins/games) desc;");
            //rs = stmt.executeQuery("SELECT * FROM highscore");
            while ( rs.next() ) {
                String queryAwnser = rs.getString("name")+";";
                queryAwnser += rs.getString("wins")+";"+rs.getString("games")+";";
                awnser += queryAwnser +";";
            }
            conn.close();
            
		} catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());     
            }
        out.write(awnser); 
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

	
	
	public static void sqlGameResult (String name1, String name1result, String name2, String name2result){
		 try {
		 	Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url,username,password);
           
            Statement stmt = conn.createStatement();
            PreparedStatement preparedStatement = null;
            ResultSet rs;
            
            
            // If Player 1 doesn't exist in Database.
            rs = stmt.executeQuery("SELECT name FROM highscore WHERE name='"+name1+"';");
            String getName1 = "";
            while ( rs.next() ) {
                getName1 += rs.getString("name");
            }
            if (getName1.equals("")){
	            preparedStatement = conn.prepareStatement("INSERT INTO highscore values (id,?,?,?)");
	            preparedStatement.setString(1, name1);
	            if(name1result.equals("win")){
	            	preparedStatement.setInt(2, 1);
	            }
	            else{
	            	preparedStatement.setInt(2, 0);
	            }
	            preparedStatement.setInt(3, 1);
	            preparedStatement.executeUpdate();
            }
            // If Player 1 already exists in database
            else if (getName1.equals(name1)) {
            	rs = stmt.executeQuery("SELECT wins, games FROM highscore WHERE name='"+name1+"';");
            	int wincount = 0;
            	int gamecount = 0;
            	while ( rs.next() ) {
                    wincount = rs.getInt("wins");
                    gamecount = rs.getInt("games");
                }
            	
	            if(name1result.equals("win")){
	            	wincount++;
	            }
	            gamecount++;
	            preparedStatement = conn.prepareStatement("UPDATE highscore SET wins="+wincount+", games="+gamecount+" WHERE name='"+name1+"';");
	            preparedStatement.executeUpdate();
            	
			}
            
            // If Player 2 doesn't exist in Database.
            rs = stmt.executeQuery("SELECT name FROM highscore WHERE name='"+name2+"';");
            String getName2 = "";
            while ( rs.next() ) {
                getName2 += rs.getString("name");
            }
            if (getName2.equals("")){
	            preparedStatement = conn.prepareStatement("INSERT INTO highscore values (id,?,?,?)");
	            preparedStatement.setString(1, name2);
	            if(name2result.equals("win")){
	            	preparedStatement.setInt(2, 1);
	            }
	            else{
	            	preparedStatement.setInt(2, 0);
	            }
	            preparedStatement.setInt(3, 1);
	            preparedStatement.executeUpdate();
            }
            
            // If Player 2 already exists in database
            else if (getName2.equals(name2)) {
            	rs = stmt.executeQuery("SELECT wins, games FROM highscore WHERE name='"+name2+"';");
            	int wincount = 0;
            	int gamecount = 0;
            	while ( rs.next() ) {
                    wincount = rs.getInt("wins");
                    gamecount = rs.getInt("games");
                }
            	
	            if(name2result.equals("win")){
	            	wincount++;
	            }
	            gamecount++;
	            preparedStatement = conn.prepareStatement("UPDATE highscore SET wins="+wincount+", games="+gamecount+" WHERE name='"+name2+"';");
	            preparedStatement.executeUpdate();
            	
			}
      
            conn.close();
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
	}
}
